﻿using System.Collections.Generic;
using Mysoft.Map.Extensions.DAL;

namespace Mysoft.Platform.DataAccess
{
	public class BooksRepository<T> where T : class, new()
	{
		/// <summary>
		/// 查询全部图书
		/// </summary>
		/// <param name="pageIndex"></param>
		/// <param name="pageSize"></param>
		/// <param name="totalPage"></param>
		/// <returns></returns>
		public List<T> SelectAll(int pageIndex, int pageSize, out int totalPage)
		{
			if( pageIndex <= 1 ) {
				pageIndex = 1;
			}
			int rowBegin = (pageIndex - 1) * pageSize + 1;
			int rowEnd = rowBegin + pageSize;
			totalPage = 0;

			return CPQuery.From("SELECT * FROM( SELECT ROW_NUMBER() OVER(ORDER BY a.BookId) AS ROW, a.*, b.CategoryName " +
			                    "FROM dbo.Books a LEFT JOIN dbo.Category b ON a.CategoryId = b.CategoryId ) t " +
			                    "WHERE t.Row >= @rowBegin AND t.Row <= @rowEnd "
			                    , new {rowBegin = rowBegin, rowEnd = rowEnd}).ToList<T>();
		}

		/// <summary>
		/// 根据类别查找图书
		/// </summary>
		/// <param name="categoryId"></param>
		/// <param name="pageIndex"></param>
		/// <param name="pageSize"></param>
		/// <param name="totalPage"></param>
		/// <returns></returns>
		public List<T> GetBooksByCategoryId(int categoryId, int pageIndex, int pageSize, out int totalPage)
		{
			if( pageIndex <= 1 ) {
				pageIndex = 1;
			}
			int rowBegin = (pageIndex - 1) * pageSize + 1;
			int rowEnd = rowBegin + pageSize;
			totalPage = 0;

			return CPQuery.From("SELECT * FROM( SELECT ROW_NUMBER() OVER(ORDER BY a.BookId) AS ROW, a.*, b.CategoryName " +
								"FROM dbo.Books a LEFT JOIN dbo.Category b ON a.CategoryId = b.CategoryId ) t " +
								"WHERE t.Row >= @rowBegin AND t.Row <= @rowEnd AND t.CategoryId = @categoryId ORDER BY t.BookName "
						  , new { rowBegin = rowBegin, rowEnd = rowEnd, categoryId = categoryId }).ToList<T>();
		}

		/// <summary>
		/// 根据书名模糊查找图书
		/// </summary>
		/// <param name="bookName"></param>
		/// <param name="pageIndex"></param>
		/// <param name="pageSize"></param>
		/// <param name="totalPage"></param>
		/// <returns></returns>
		public List<T> GetBooksByBookName(string bookName, int pageIndex, int pageSize, out int totalPage)
		{
			if( pageIndex <= 1 ) {
				pageIndex = 1;
			}
			int rowBegin = (pageIndex - 1) * pageSize + 1;
			int rowEnd = rowBegin + pageSize;
			totalPage = 0;

			return CPQuery.From("SELECT * FROM( SELECT ROW_NUMBER() OVER(ORDER BY a.BookId) AS ROW, a.*, b.CategoryName " +
								"FROM dbo.Books a LEFT JOIN dbo.Category b ON a.CategoryId = b.CategoryId ) t " +
								"WHERE t.Row >= @rowBegin AND t.Row <= @rowEnd AND t.BookName LIKE @bookName ORDER BY t.BookName"
						 , new { rowBegin = rowBegin, rowEnd = rowEnd, bookName = bookName }).ToList<T>();
		}
	}
}